package key;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Database {
	Log log = null;
	private Connection con = null;
	
	public Database(){
		log = new Log();
		con = createConnection();
	}
	
	/**
	 * 创建数据库连接类
	 * @return Connection对象
	 */
	public Connection createConnection() {
		
		Connection con = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();//加载数据库驱动
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/try21?useSSL=false&autoReconnect=true&autoReconnectForPools=true", "cxlm", "try21");//获取数据库连接
			if (con != null) {
				System.out.println("数据库连接成功");
			}
			} catch (Exception e) {
				e.printStackTrace();
				System.out.println("发生异常，数据库连接失败");
				log.writeIn("Database: 数据库连接失败: 0000");
			} 
		return con;
	}
	
	/**
	 * 判断用户名与密码是否匹配
	 * @param uname 用户名
	 * @param pass 密码
	 * @return 布尔值
	 */
	public boolean login(String uname, String pass){
		
		String sql = "select pass from user where uname = '"+uname+"';";
		try{
        	if(con==null){
        		System.out.println("莫名其妙地发生了意外");
 				log.writeIn("Database: 数据库连接意外断开: 0010");
        	}else{
        		PreparedStatement ps = con.prepareStatement(sql);
        		ResultSet rs = ps.executeQuery();//执行查询
        		while(rs.next()){//遍历结果集
        			sql = rs.getString(1);
        		}
        	}
        } catch(SQLException e) {
        	e.printStackTrace();
        	System.out.println("见鬼，出意外了");
				log.writeIn("Database: SQLException"+ e +": 0011");
        }
		
		if(sql.equals(pass)){
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * 通过用户名获取用户id
	 * @param uname 用户名
	 * @return 用户id
	 */
	public String getUid(String uname) throws SQLException{
		
		String uid = null;
		String sql = "select uid from user where uname = '"+uname+"';";
        		PreparedStatement ps = con.prepareStatement(sql);
        		ResultSet rs = ps.executeQuery();//执行查询
        		while(rs.next()){//遍历结果集
        			uid = rs.getString(1);
        }
		return uid;
	}
	
	/**
	 * 创建结果列表方法
	 * @param uname 用户名
	 * @param uid 用户id
	 * @return 信息列表
	 */
	public List<Info> createList(String uname, String uid){
		
		List<Info> timeList = new ArrayList<>();
		try{
			PreparedStatement ps = con.prepareStatement("select * from time");
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				String uName = null;
				String uId = rs.getString("uid");
            
				if(uId.equals("0")){//填写表格
					uName = "Empty";
				}else if(uId.equals("-1")){
					uName = "Disable";
				}else if(uId.equals(uid)){
					uName = uname;
				}else{
					PreparedStatement ps2 = con.prepareStatement("select uname from user where uid='"+uId+"';");
					ResultSet rs2 = ps2.executeQuery();
					while(rs2.next()){//遍历结果集
						uName = rs2.getString(1);
					}
					/*ps2.close();
            		rs2.close();*/
				}
				String tId = rs.getString("tid");
				Info user = new Info(uName, tId);
				timeList.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			log.writeIn("Database: SQL发生异变0001");
		}
	return timeList;
	}
	
	/**
	 * 用于将选定时间与用户id匹配起来
	 * @param toAdd 表单返回的数组
	 * @param uid 用户id
	 */
	public void choose(String[] toAdd, int uid){
		
		int nn = 0;
		
		for(String str:toAdd){
			int tid = Integer.parseInt(str);
			try{
				String sql = "update time set uid=? where tid=?";
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setInt(1, uid);
				ps.setInt(2, tid);
				nn += ps.executeUpdate();
			
			}catch(SQLException e){
				e.printStackTrace();
				System.out.println("迷之错误。。。");
				log.writeIn("Database: 异常，SQLException"+e+"0001");
			}
		}
		log.writeIn("Database: 信息添加成功，更新"+nn+"条数据");
	}
	
	/**
	 * 用于将选定时间从指定的用户id中删除
	 * @param toDel 页面返回的数组
	 * @param uid 用户id
	 */
	public void delete(String[] toDel, int uid){
		
		int nn = 0;
		
		for(String str:toDel){
			int tid = Integer.parseInt(str);
			try{
				String sql = "update time set uid=0 where tid=?";
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setInt(1, tid);
				nn += ps.executeUpdate();
			
			}catch(SQLException e){
				e.printStackTrace();
				System.out.println("迷之错误。。。");
				log.writeIn("Database: 异常，SQLException"+e+"0006");
			}
		}
		log.writeIn("信息删除成功，更新"+nn+"条数据");
	}
	
	/**
	 * 通过页面获得的字符串新建用户
	 * @param uidS 用户id
	 * @param uname 用户名
	 * @param pass 密码
	 * @param dep 所属部门 目前没有用
	 * @return 更新数据条数
	 */
	public int addUser(String uidS, String uname, String pass, String dep){
		
		int nn = 0;
		int uid = Integer.parseInt(uidS);
		
		try{
			String sql = "insert into user(uid,uname,pass,dep)values(?,?,?,?)";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, uid);
			ps.setString(2, uname);
			ps.setString(3, pass);
			ps.setString(4, dep);
			nn = ps.executeUpdate();
			log.writeIn("Database: "+uname+"注册成功\n->用户id:"+uid+"\n->密码:"+pass+"\n->所属部门:"+dep+"\n->数据库更新"+nn+"条数据");
			
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("迷之错误。。。");
			log.writeIn("Database: 异常，SQLException"+e+"0001");
		}
		return nn;
	}
	
	/**
	 * 获取当前周次信息
	 * @return 周次信息
	 */
	public String getWeek(){
		
		String week = null;
		try{
			String sql = "select week from master where inde=1";
			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();//执行查询
			while(rs.next()){//遍历结果集
				week = rs.getString(1);
			}
		} catch(SQLException e){
			log.writeIn("Database: 周次信息查询失败 "+e);
		}
		return week;
	}
	
	/**
	 * 获取当前公告
	 * @return 公告内容
	 */
	public String getNotice(){
		
		String not = null;
		try{
			String sql = "select noti from master where inde=1";
			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();//执行查询
			while(rs.next()){//遍历结果集
				not = rs.getString(1);
			}
		} catch(SQLException e){
			log.writeIn("Database: 周次信息查询失败 "+e);
		}
		return not;
	}
	
	/**
	 * 查询用户选中的时间总长
	 * @param uid 用户id
	 * @return 总时长
	 */
	public int getCount(int uid){
		
		int total = 0;
		try{
			String sql = "select count(*) from time where uid="+uid;
			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();//执行查询
			rs.next();
			total = rs.getInt(1);
		} catch(SQLException e) {
			e.printStackTrace();
			log.writeIn("Database: 查询到" + total + "条数据");
		}
		
		return total;
		
	}
	
	/**
	 * 管理员专用方法：添加信息
	 * @param uid 用户id
	 * @param tid 时间id
	 */
public void madd(int uid, int tid){
		
		int nn = 0;
		
		try{
			String sql = "update time set uid=? where tid=?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, uid);
			ps.setInt(2, tid);
			nn += ps.executeUpdate();
		
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("迷之错误。。。");
			log.writeIn("Database: 异常，SQLException"+e+"0021");
		}
	log.writeIn("Database: 管理员添加了"+nn+"条数据");
	}
	
	/**
	 * 管理员专用方法：重命名
	 * @param uid 用户id
	 * @param newName 新用户名
	 */
	public void rename(int uid, String newName){
	
		try{
			String sql = "update user set uname=? where uid=?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, newName);
			ps.setInt(2, uid);
			ps.executeUpdate();
	
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("迷之错误，已写入日志");
			log.writeIn("Database: 异常，SQLException"+e+"0023");
		}
		log.writeIn("Database: 管理员将uid为"+uid+"的用户重命名为"+newName);
	}
	
	/**
	 * 管理员专用方法：设置公告
	 * @param notice 公告内容
	 */
	public void setNotice(String notice){
		try{
			String sql = "update master set noti=? where inde=1";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, notice);
			ps.executeUpdate();
	
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("迷之错误，已写入日志");
			log.writeIn("Database: 异常，SQLException"+e+"0024");
		}
		log.writeIn("Database: 管理员将公告修改为：【"+notice+"】");
	}
	
	/**
	 * 管理员专用方法：设置周次
	 * @param week 当前周次
	 */
	public void setWeek(String week){
		try{
			String sql = "update master set week=? where inde=1";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, week);
			ps.executeUpdate();
	
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("迷之错误，已写入日志");
			log.writeIn("Database: 异常，SQLException"+e+"0025");
		}
		log.writeIn("Database: 管理员将周次修改为：【"+week+"】");
	}
	
	/**
	 * 管理员专用：删除信息
	 * @param toDel 从页面接受的数组
	 */
	public void mdelete(String[] toDel){
		
		int nn = 0;
		
		for(String str:toDel){
			int tid = Integer.parseInt(str);
			try{
				String sql = "update time set uid=0 where tid=?";
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setInt(1, tid);
				nn += ps.executeUpdate();
			
			}catch(SQLException e){
				e.printStackTrace();
				System.out.println("迷之错误。。。");
				log.writeIn("Database: 异常，SQLException"+e+"0026");
			}
		}
		log.writeIn("管理员删除了"+nn+"条数据");
	}

	/**
	 * 管理员专用：将指定时间设为不可用
	 * @param toDel 页面接受的数组
	 */
	public void mdis(String[] toDel) {
		int nn = 0;
		
		for(String str:toDel){
			int tid = Integer.parseInt(str);
			try{
				String sql = "update time set uid=-1 where tid=?";
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setInt(1, tid);
				nn += ps.executeUpdate();
			
			}catch(SQLException e){
				e.printStackTrace();
				System.out.println("迷之错误。。。");
				log.writeIn("Database: 异常，SQLException"+e+"0027");
			}
		}
		log.writeIn("管理员将"+nn+"段时间设为不可用");
	}

	/**
	 * 获取当前时长上限
	 * @return 时长
	 */
	public int getMax(){
		String max = null;
		try{
			String sql = "select max from master where inde=1";
			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();//执行查询
			while(rs.next()){//遍历结果集
				max = rs.getString(1);
			}
		} catch(SQLException e){
			log.writeIn("Database: 上限信息查询失败 "+e);
		}
		return Integer.parseInt(max);
	}
	
	/**
	 * 管理员专用：设置时长上限
	 * @param max 上限值，int型
	 */
	public void setMax(int max){
		try{
			String sql = "update master set max=? where inde=1";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, max);
			ps.executeUpdate();
	
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("迷之错误，已写入日志");
			log.writeIn("Database: 异常，SQLException"+e+"0030");
		}
		log.writeIn("Database: 管理员将时长上限修改为：【"+max+"】");
	}
	
	/**
	 * 管理员专用：重设密码
	 * @param uid 用户id
	 * @param npass 新密码
	 */
	public void mpass(int uid, String npass){
		
		try{
			String sql = "update user set pass=? where uid=?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, npass);
			ps.setInt(2, uid);
			ps.executeUpdate();
	
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("迷之错误，已写入日志");
			log.writeIn("Database: 异常，SQLException"+e+"0033");
		}
		log.writeIn("Database: 管理员将uid为"+uid+"用户的密码重设为"+npass);
	}
	
	/**
	 * 管理员专用：删除用户（从用户列表）
	 * @param uid 用户id
	 */
	public void delU(int uid){
		try{
			String sql = "delete from user where uid=?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, uid);
			ps.executeUpdate();
	
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("迷之错误，已写入日志");
			log.writeIn("Database: 异常，SQLException"+e+"0034");
		}
		log.writeIn("Database: 管理员将uid为"+uid+"的用户从用户列表中删除");
	}
	
	/**
	 * 管理员专用：删除用户（从时间列表）
	 * @param uid 用户id
	 */
	public void delT(int uid){
		int nn = 0;
		try{
			String sql = "update time set uid=0 where uid=?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, uid);
			nn = ps.executeUpdate();
	
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("迷之错误，已写入日志");
			log.writeIn("Database: 异常，SQLException"+e+"0035");
		}
		log.writeIn("Database: 管理员将uid为"+uid+"的用户从时间列表中删除，更新"+nn+"条数据");
	}
	
	/**
	 * 查找数据库是否存在指定的用户名
	 * @return boolean
	 */
	public boolean isExist(String uname){
		try{
			String sql = "select uid from user where uname=?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, uname);
			ResultSet rs = ps.executeQuery();//执行查询
			return rs.next();
		} catch(SQLException e){
			log.writeIn("Database: 查找用户名失败 "+e);
		}
		return false;
	}
}
